package com.ljc.cjgl.repository;

import java.util.List;
import java.util.Optional;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import com.ljc.cjgl.entitiy.Stu;
import com.ljc.cjgl.entitiy.TbClass;
import com.ljc.cjgl.entitiy.TbTest;
import com.ljc.cjgl.entitiy.TestRegister;
@Repository
public interface TestRegisterResitory extends JpaRepository<TestRegister, Integer>, JpaSpecificationExecutor<TestRegister> {

	@Query(value="select distinct u.test from TestRegister u order by u.test.testDate")
	public List<TbTest> findDistinctTest();
	
	@Query(value="select distinct u.student.tbClass from TestRegister u")
	public List<TbClass> findDistinctStudentTbClass();

	public List<Integer> findDistinctStudentIdByStudentTbClassId(Integer classId);

	public List<Integer> findDistinctStudentIdByStudentTbClassIdAndTestId(Integer classId, Integer testId);

	public List<TestRegister> findByStudentTbClassIdAndTestId(Integer classId, Integer testId);
	
	@Query(value="select distinct t.test.xq from TestRegister t")
	public List<String> getTestXq();
	
	@Query(value="select distinct t.test from TestRegister t "
			+ "where t.student.tbClass.id=?1 "
			+ "and t.test.xq=?2 "
			+ "order by t.test.testDate")
	public List<TbTest> findTestNameByClassIdAndXq(Integer classId, String xq);

	public Optional<TestRegister> findByStudentIdAndTestId(Integer id, Integer id2);

	public List<TestRegister> findByStudentId(Integer id);

	public Page<TestRegister> findByIsShow(boolean b, Pageable pageRequest);
	
	@Query(value="from TestRegister t where t.student.tbClass.id=?1 order by t.test.testDate desc")
	public List<TestRegister> findByStudentTbClassId(Integer id);
	
	@Query(value="select distinct t.student.tbClass from TestRegister t where t.test.id=?1")
	public List<TbClass> findTbClassByTestId(Integer testId);
	
	@Query(value="select distinct t.student from TestRegister t where t.test.id=?1")
	public List<Stu> findStuByTestId(Integer id);
	
	@Query(value="select distinct t.student from TestRegister t ")
	public List<Stu> findDistinctStu();

}
